/*
	BEGIN
	DECLARE @revisionNumber INT
	EXEC sp_open_purchase_order_audit_trail 3, 1000, '02/13/2005', '02/09/2005', 'wqw', 20, 30, 'admin', @revisionNumber OUT
	PRINT @revisionNumber
	END	
*/

ALTER    PROC sp_open_purchase_order_audit_trail( @purchaseOrder_id INT, 
	@maxAmt VARCHAR(50), 
	@validFrom VARCHAR(50), 
	@validTo VARCHAR(50), 
	@vendorQuote VARCHAR(50), 
	@financeHold INT, 
	@operationsHold INT, 
	@user_id VARCHAR(50),
	@revisionNumber INT OUT )
AS
BEGIN
	DECLARE @prev_maxAmt VARCHAR(50)
	DECLARE @prev_validFrom VARCHAR(50)
	DECLARE @prev_validTo VARCHAR(50)
	DECLARE @prev_vendorQuote VARCHAR(50)
	DECLARE @prev_financeHold INT
	DECLARE @prev_operationsHold INT
	DECLARE @prev_revision_number INT
	DECLARE @comments VARCHAR(1000)
	DECLARE @holdString VARCHAR(10)
	DECLARE @prevHoldString VARCHAR(10)
	DECLARE @revsionNumberIncremented INT

	SELECT @prev_vendorQuote = VendorQuote, @prev_maxAmt = PO_Max_Value, @prev_validFrom = CONVERT( VARCHAR, PO_Valid_From, 101), @prev_validTo = CONVERT( VARCHAR, PO_Valid_To, 101), @revisionNumber = PO_revision_No, @prev_revision_number = PO_revision_No, @prev_financeHold = PO_Finance_Hold, @prev_operationsHold = PO_Operation_Hold FROM PurchaseOrder_Table WHERE PurchaseOrder_ID = @purchaseOrder_id

	SET @comments = ''
	SET @revsionNumberIncremented = 0
	
	IF @prev_vendorQuote <> @vendorQuote
	BEGIN
		IF @revsionNumberIncremented <> 1
		BEGIN
			SET @revisionNumber = @prev_revision_number + 1
			SET @revsionNumberIncremented = 1
		END
		SET @comments = @comments + '<br> Vendor Quotation changed from: <b><I>' + @prev_vendorQuote + '</b></I> to <b><I>' + @vendorQuote + '</b></I>'
	END

	IF @prev_maxAmt <> @maxAmt
	BEGIN
		IF @revsionNumberIncremented <> 1
		BEGIN
			SET @revisionNumber = @prev_revision_number + 1
			SET @revsionNumberIncremented = 1
		END
		SET @comments = @comments + '<br> Max $ changed from: <b><I>' + @prev_maxAmt + '</b></I> to <b><I>' + @maxAmt + '</b></I>'
	END

	IF @prev_validFrom <> @validFrom
	BEGIN
		IF @revsionNumberIncremented <> 1
		BEGIN
			SET @revisionNumber = @prev_revision_number + 1
			SET @revsionNumberIncremented = 1
		END
		SET @comments = @comments + '<br> Valid From changed from: <b><I>' + @prev_validFrom + '</b></I> to <b><I>' + @validFrom + '</b></I>'
	END

	IF @prev_validTo <> @validTo
	BEGIN
		IF @revsionNumberIncremented <> 1
		BEGIN
			SET @revisionNumber = @prev_revision_number + 1
			SET @revsionNumberIncremented = 1
		END
		SET @comments = @comments + '<br> Valid To changed from: <b><I>' + @prev_validTo + '</b></I> to <b><I>' + @validTo + '</b></I>'
	END

	IF @prev_financeHold <> @financeHold
	BEGIN
		IF @revsionNumberIncremented <> 1
		BEGIN
			SET @revisionNumber = @prev_revision_number + 1
			SET @revsionNumberIncremented = 1
		END
		
		IF @prev_financeHold = 0
		BEGIN
			SET @prevHoldString = 'Approved'
		END
		ELSE IF @prev_financeHold = -1
		BEGIN
			SET @prevHoldString = 'X'
		END
		ELSE
		BEGIN
			SET @prevHoldString = CONVERT( VARCHAR, @prev_financeHold )
		END
	
		IF @financeHold = 0
		BEGIN
			SET @holdString  = 'Approved'
		END
		ELSE IF @financeHold = -1
		BEGIN
			SET @holdString  = 'X'
		END
		ELSE
		BEGIN
			SET @holdString  = CONVERT( VARCHAR, @financeHold )
		END

		SET @comments = @comments + '<br> Finance Hold changed from: <b><I>' + @prevHoldString + '</b></I> to <b><I>' + @holdString + '</b></I>'
	END

	IF @prev_operationsHold <> @operationsHold
	BEGIN
		IF @prev_operationsHold = 0
		BEGIN
			SET @prevHoldString = 'Approved'
		END
		ELSE IF @prev_operationsHold = -1
		BEGIN
			SET @prevHoldString = 'X'
		END
		ELSE
		BEGIN
			SET @prevHoldString = CONVERT( VARCHAR, @prev_operationsHold )
		END
	
		IF @operationsHold = 0
		BEGIN
			SET @holdString  = 'Approved'
		END
		ELSE IF @operationsHold = -1
		BEGIN
			SET @holdString  = 'X'
		END
		ELSE
		BEGIN
			SET @holdString  = CONVERT( VARCHAR, @operationsHold )
		END


		IF @revsionNumberIncremented <> 1
		BEGIN
			SET @revisionNumber = @prev_revision_number + 1
			SET @revsionNumberIncremented = 1
		END
		SET @comments = @comments + '<br> Operations Hold changed from: <b><I>' + @prevHoldString + '</b></I> to <b><I>' + @holdString + '</b></I>'
	END
	IF ( LEN( RTRIM( LTRIM ( @comments ) ) ) > 0 )
	BEGIN
    	INSERT INTO PurchaseOrder_Audit_Trail( PurchaseOrder_ID, [User_Id], Actions, TimeStamp )
		VALUES( @purchaseOrder_id, @user_id, @comments, CURRENT_TIMESTAMP )
    END
END